Redshiftでテーブルのカラム番号・名前・型・桁数を抽出する

#Amazon Redshift

#SQL

k.uehara

2023.02.21

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

データアナリティクス事業本部のueharaです。

今回はAmazon Redshiftでテーブルのカラム番号・名前・型・桁数を抽出するという処理をしてみたいと思います。

方法1

SVV_COLUMNSを利用して、以下のように書くことができます。

SELECT 
  table_schema AS schema_name,
  table_name,
  ordinal_position AS column_number,
  column_name,
  data_type,
  character_maximum_length,
  numeric_precision,
  numeric_scale
FROM SVV_COLUMNS
-- 確認不要なスキーマをフィルタ
WHERE table_schema NOT IN ('admin', 'information_schema', 'pg_catalog')
ORDER BY schema_name, table_name, column_number

結果は、例えば次のようになります。

schema_name	table_name	column_number	column_name	data_type	character_maximum_length	numeric_precision	numeric_scale
schema_a	table_a	1	column_1	character varying	16	[NULL]	[NULL]
schema_a	table_a	2	column_2	date	[NULL]	[NULL]	[NULL]
schema_a	table_a	3	column_3	numeric	[NULL]	32	2
schema_a	table_b	1	column_1	integer	[NULL]	32	0

NUMERIC(precision, scale)といった、precisionとscaleの桁数をそれぞれカラムを分けて表示したい場合は、こちらを使うと良いと思います。

該当しない項目（例：VARCHARにおけるnumeric_precision等）にはNULLが入ります。

方法2

pg_attribute, pg_class, pg_namespaceを利用して、以下のように書くことができます。

SELECT
  ns.nspname AS schema_name,
  cls.relname AS table_name,
  atr.attnum AS column_number,
  atr.attname AS column_name,
  pg_catalog.format_type(atr.atttypid, atr.atttypmod) AS data_type
FROM pg_attribute atr
JOIN pg_class cls ON atr.attrelid = cls.oid
JOIN pg_namespace ns ON cls.relnamespace = ns.oid
WHERE
  column_number > 0 AND
  NOT atr.attisdropped AND
  -- 確認不要なスキーマをフィルタ
  schema_name NOT LIKE 'pg_%' AND
  schema_name NOT IN ('admin', 'information_schema')
ORDER BY schema_name, table_name, column_number

結果は、例えば次のようになります。

schema_name	table_name	column_number	column_name	data_type
schema_a	table_a	1	column_1	character varying(16)
schema_a	table_a	2	column_2	date
schema_a	table_a	3	column_3	numeric(32,2)
schema_a	table_b	1	column_1	integer

データ型と桁数を同じカラム内に表現したい場合は、こちらを使うと良いと思います。

その他の方法

カラムの型・桁を抽出したいテーブルが属するスキーマがsearch_pathに設定されている場合、PG_TABLE_DEFを利用して次のように書くこともできます。

set_pathについては公式ドキュメントをご確認下さい。

WITH tmp AS (
  SELECT 
    table_schema AS schema_name,
    table_name,
    ordinal_position AS column_number,
    column_name
  FROM SVV_COLUMNS
)
SELECT
  schemaname AS schema_name,
  tablename AS table_name,
  column_number,
  "column" AS column_name,
  type AS data_type
FROM PG_TABLE_DEF ptd
JOIN tmp ON
  ptd.schemaname = tmp.schema_name AND
  ptd.tablename = tmp.table_name AND
  ptd."column" = tmp.column_name
-- 確認不要なスキーマをフィルタ
WHERE schema_name NOT IN ('pg_catalog')
ORDER BY schema_name, table_name, column_number

結果は「方法2」の表示結果と同じになります。